﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;
namespace FilmekAdatbazis
{
    class szamla : DAL
    {
        private System.Data.SqlClient.SqlDataAdapter daGrid;
		private System.Data.SqlClient.SqlDataAdapter daAruk; 
		private System.Data.DataSet dsArukGrid;
		private System.Data.DataSet dsAruk;
		private System.Data.DataSet dsEgyAruSor;
		private System.Data.SqlClient.SqlDataAdapter daEgyAruSor;
		private string ErrMess;
		private string sQuery;     
		public szamla(ref bool Err)
		{
			if (!base.IsConnectCreated())
			{
				Err = !base.MakeConnection();
			}
			daAruk = new SqlDataAdapter();
			daAruk.TableMappings.Add("Table", "Szamlazas");
			daEgyAruSor = new SqlDataAdapter();
			daEgyAruSor.TableMappings.Add("Table", "EgyAru");
			//			dsArukGrid = new DataSet("ArukGrid");
			daGrid = new SqlDataAdapter();
            daGrid.TableMappings.Add("Table", "ArukGrid");
		}

        public System.Data.DataSet SzamlaGridTolt(int id ,ref string ErrM)
        {
            string sSQL = "SELECT DISTINCT FelhasznaloNev, Mikortol, Meddig, Cim, Stilus FROM Stilusok s, Filmek f, DVDk d, Felhasznalok fl, Kolcsonzesek k, Szamla_elemek sze, Szamlazas sz WHERE s.StilusID = f.StilusID AND f.FilmID = d.FilmID AND d.Raktarszam = k.Raktarszam AND k.FelhasznaloID = fl.FelhasznaloID AND sze.KolcsonzesID = k.KolcsonzID AND sz.SzamlaID = sz.SzamlaID AND sze.SzamlaID ="+ id;
            dsArukGrid = base.myFillDataSetFromString(sSQL, "ArukGrid", ref daGrid, ref ErrM);
            return dsArukGrid;
        }

        public int maxSzamlaElem(ref string ErrM)
        {
            string sSQL = "SELECT MAX(SzamlaID) FROM Szamla_elemek";
            int id = base.myExecuteScalar(sSQL, ref ErrM);
            return id;
        }

        public void addSzamla(int id1,int id2, int vevoID,DateTime datum, double fizetett,ref string ErrM)
        {
            string d = datum.ToString();
            string f = fizetett.ToString();
            
            string sSQL = "INSERT INTO Szamlazas VALUES (" + id1 + "," + id2 + "," + vevoID + ",'" + d+"','" + f + "')";
            base.myInsertCommand(sSQL, daAruk, ref ErrMess);
        }

        public void anulal(ref string ErrM)
        {
            string sSQL = "ROLLBACK TRANSACTION";
            base.myExecuteScalar(sSQL, ref ErrM);
        }

        public System.Data.SqlClient.SqlDataReader getFelhasznalok(ref string ErrM)
        {
            myDataReader = base.myExecuteReader("SELECT FelhasznaloNev FROM Felhasznalok",ref ErrM);
            return myDataReader;
        }

        public System.Data.SqlClient.SqlDataReader getMikortolMeddig(int id,ref string ErrM)
        {
            myDataReader = base.myExecuteReader("SELECT DISTINCT Mikortol, Meddig FROM Kolcsonzesek k, Szamla_elemek sz WHERE k.KolcsonzID = sz.KolcsonzesID AND sz.SzamlaID ="+id, ref ErrM);
            return myDataReader;
        }

        public System.Data.SqlClient.SqlDataReader getFelhasznaloId(string nev, ref string ErrM)
        {
            myDataReader = base.myExecuteReader("SELECT FelhasznaloID FROM Felhasznalok f WHERE f.FelhasznaloNev = '" + nev + "'", ref ErrM);
            return myDataReader;
        }

        public void torolSzamla(int id, ref string ErrM)
        {
            string query = "DELETE FROM Szamla_elemek WHERE SzamlaID ="+id;
            base.myExecuteScalar(query, ref ErrM);
        }

        public System.Data.SqlClient.SqlDataReader getSzamlaID(ref String ErrM)
        {
            myDataReader = base.myExecuteReader("SELECT MAX(SzamlazasID) FROM Szamlazas", ref ErrM);
            return myDataReader;
        }

        public System.Data.SqlClient.SqlDataReader getCodFiscal(String nev, ref String Errm)
        {
            myDataReader = base.myExecuteReader("SELECT cod_fiscal FROM Felhasznalok f WHERE f.FelhasznaloNev ='"+nev+"'", ref Errm);
            return myDataReader;
        }
    }
}
